﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;

//Please add references
namespace CrazyTalk.MSSQLDAL
{
	/// <summary>
	/// 数据访问类:UserPermissionListDAL
	/// </summary>
	public partial class UserPermissionListDAL:IUserPermissionListDAL
	{
		public UserPermissionListDAL()
		{}
		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("MenuID", "UserPermissionList"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(string UserId,int MenuID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from UserPermissionList");
			strSql.Append(" where UserId=@UserId and MenuID=@MenuID ");
			SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20),
					new SqlParameter("@MenuID", SqlDbType.Int,4)			};
			parameters[0].Value = UserId;
			parameters[1].Value = MenuID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(UserPermissionList model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into UserPermissionList(");
			strSql.Append("UserId,MenuID,Type,CreateTime,EditTime)");
			strSql.Append(" values (");
			strSql.Append("@UserId,@MenuID,@Type,@CreateTime,@EditTime)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20),
					new SqlParameter("@MenuID", SqlDbType.Int,4),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@EditTime", SqlDbType.DateTime)};
			parameters[0].Value = model.UserId;
			parameters[1].Value = model.MenuID;
			parameters[2].Value = model.Type;
			parameters[3].Value = model.CreateTime;
			parameters[4].Value = model.EditTime;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(UserPermissionList model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update UserPermissionList set ");
			strSql.Append("Type=@Type,");
			strSql.Append("CreateTime=@CreateTime,");
			strSql.Append("EditTime=@EditTime");
			strSql.Append(" where RecordId=@RecordId");
			SqlParameter[] parameters = {
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@EditTime", SqlDbType.DateTime),
					new SqlParameter("@RecordId", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.VarChar,20),
					new SqlParameter("@MenuID", SqlDbType.Int,4)};
			parameters[0].Value = model.Type;
			parameters[1].Value = model.CreateTime;
			parameters[2].Value = model.EditTime;
			parameters[3].Value = model.RecordId;
			parameters[4].Value = model.UserId;
			parameters[5].Value = model.MenuID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}



        /// <summary>
        /// 更新用户的所有权限
        /// </summary>
        /// <param name="uid">需要更新权限的用户ID</param>
        /// <param name="list">用户的新权限列表</param>
        /// <returns></returns>
        public bool UpdateList(string uid, List<Strs> list)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> param = new List<SqlParameter>();
            //List<string> clist = new List<string>();
            //int num = 0;

            strSql.Append(@" if exists(select * from UserPermissionList where UserId=@UserId0 )
                            begin
	                            delete from UserPermissionList where UserId=@UserId0
                            end  ");
            //clist.Add("UserId");
            param.Add(new SqlParameter("@UserId0", uid));

            if (list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    strSql.Append(@"   insert into UserPermissionList(UserId,MenuId,Type,CreateTime,EditTime)
                                       values(@UserId0,@MenuId" + i + ",'1',GETDATE(),GETDATE())   ");
                    param.Add(new SqlParameter("@MenuId" + i, list[i].str1));
                }
            }
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), param.ToArray());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int RecordId)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from UserPermissionList ");
			strSql.Append(" where RecordId=@RecordId");
			SqlParameter[] parameters = {
					new SqlParameter("@RecordId", SqlDbType.Int,4)
			};
			parameters[0].Value = RecordId;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(string UserId,int MenuID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from UserPermissionList ");
			strSql.Append(" where UserId=@UserId and MenuID=@MenuID ");
			SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.VarChar,20),
					new SqlParameter("@MenuID", SqlDbType.Int,4)			};
			parameters[0].Value = UserId;
			parameters[1].Value = MenuID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string RecordIdlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from UserPermissionList ");
			strSql.Append(" where RecordId in ("+RecordIdlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public UserPermissionList GetModel(int RecordId)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 RecordId,UserId,MenuID,Type,CreateTime,EditTime from UserPermissionList ");
			strSql.Append(" where RecordId=@RecordId");
			SqlParameter[] parameters = {
					new SqlParameter("@RecordId", SqlDbType.Int,4)
			};
			parameters[0].Value = RecordId;

			UserPermissionList model=new UserPermissionList();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["RecordId"]!=null && ds.Tables[0].Rows[0]["RecordId"].ToString()!="")
				{
					model.RecordId=int.Parse(ds.Tables[0].Rows[0]["RecordId"].ToString());
				}
				if(ds.Tables[0].Rows[0]["UserId"]!=null && ds.Tables[0].Rows[0]["UserId"].ToString()!="")
				{
					model.UserId=ds.Tables[0].Rows[0]["UserId"].ToString();
				}
				if(ds.Tables[0].Rows[0]["MenuID"]!=null && ds.Tables[0].Rows[0]["MenuID"].ToString()!="")
				{
					model.MenuID=int.Parse(ds.Tables[0].Rows[0]["MenuID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["Type"]!=null && ds.Tables[0].Rows[0]["Type"].ToString()!="")
				{
					model.Type=int.Parse(ds.Tables[0].Rows[0]["Type"].ToString());
				}
				if(ds.Tables[0].Rows[0]["CreateTime"]!=null && ds.Tables[0].Rows[0]["CreateTime"].ToString()!="")
				{
					model.CreateTime=DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
				}
				if(ds.Tables[0].Rows[0]["EditTime"]!=null && ds.Tables[0].Rows[0]["EditTime"].ToString()!="")
				{
					model.EditTime=DateTime.Parse(ds.Tables[0].Rows[0]["EditTime"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(List<string> fields, List<object> values)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select RecordId,UserId,MenuID,Type,CreateTime,EditTime ");
			strSql.Append(" FROM UserPermissionList where 1=1 " );
			List<SqlParameter> parm = new List<SqlParameter>();			if (fields != null && fields.Count > 0)
			{
				 for (int i = 0; i < fields.Count; i++)
			{
			if (fields[i].ToLower().StartsWith("like_")){strSql.Append(" AND CHARINDEX(@" + fields[i] + "," + fields[i].Split('_')[1] + ")>0 ");}
			else if (fields[i].ToLower().StartsWith("start_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")>=0");}
			else if (fields[i].ToLower().StartsWith("end_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")<=0");}
			else{strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");}
			parm.Add(new SqlParameter("@" + fields[i], values[i]));
			}
			}
			return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,List<string> fields, List<object> values,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" RecordId,UserId,MenuID,Type,CreateTime,EditTime ");
			strSql.Append(" FROM UserPermissionList where 1=1 " );
			List<SqlParameter> parm = new List<SqlParameter>();			if (fields != null && fields.Count > 0)
			{
				 for (int i = 0; i < fields.Count; i++)
			{
			if (fields[i].ToLower().StartsWith("like_")){strSql.Append(" AND CHARINDEX(@" + fields[i] + "," + fields[i].Split('_')[1] + ")>0 ");}
			else if (fields[i].ToLower().StartsWith("start_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")>=0");}
			else if (fields[i].ToLower().StartsWith("end_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")<=0");}
			else{strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");}
			parm.Add(new SqlParameter("@" + fields[i], values[i]));
			}
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(List<string> fields, List<object> values)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM UserPermissionList where 1=1 ");
			List<SqlParameter> parm = new List<SqlParameter>();			if (fields != null && fields.Count > 0)
			{
				 for (int i = 0; i < fields.Count; i++)
			{
			if (fields[i].ToLower().StartsWith("like_")){strSql.Append(" AND CHARINDEX(@" + fields[i] + "," + fields[i].Split('_')[1] + ")>0 ");}
			else if (fields[i].ToLower().StartsWith("start_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")>=0");}
			else if (fields[i].ToLower().StartsWith("end_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")<=0");}
			else{strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");}
			parm.Add(new SqlParameter("@" + fields[i], values[i]));
			}
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(List<string> fields, List<object> values, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.RecordId desc");
			}
			strSql.Append(")AS Row, T.*  from UserPermissionList T ");
			List<SqlParameter> parm = new List<SqlParameter>();			if (fields != null && fields.Count > 0)
			{
				 for (int i = 0; i < fields.Count; i++)
			{
			if (fields[i].ToLower().StartsWith("like_")){strSql.Append(" AND CHARINDEX(@" + fields[i] + "," + fields[i].Split('_')[1] + ")>0 ");}
			else if (fields[i].ToLower().StartsWith("start_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")>=0");}
			else if (fields[i].ToLower().StartsWith("end_")){strSql.Append(" AND DATEDIFF(day,@" + fields[i] + "," + fields[i].Split('_')[1] + ")<=0");}
			else{strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");}
			parm.Add(new SqlParameter("@" + fields[i], values[i]));
			}
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
		}

		#endregion  Method
	}
}

